# By Katie Cunningham
# This script gets the hours from a custom field on smd's trac, and totals them.
# The result is a low-high estimate by user.

# Requires pysqlite2
# Requires getTicketChanges (http://osiris.indyne.hq.nasa.gov/trac/smd/browser/tracScripts/getTicketChanges.py)


from datetime import datetime
from time import mktime

import sqlite
import os
import sys

from getTicketChanges import openDB, getMilestones, printTix

# Gets all of the contents of the hours field, given a certain milestone. 
# Returns that raw data.
def getHourFieldContents(cur, milestone):
	sqlcmd = "select c.value from ticket_custom c left join ticket t on (ticket = id) where t.milestone = '" + milestone + "'"
	cur.execute(sqlcmd)
	hours = cur.fetchall()
	return hours

# Does a LOT of cleaning of the raw hours array. In the end, you get an array
# with all hour estimates in their own array item, with this format:
# user, [hour-low, hour-high] (if the user provided a low-high estimate)
# or
# user, [hour] (if the user proviced just one number
def cleanHoursArray(hours):
	print "Coming in: "
	print hours
	tmp = hours
	hours = []
	for item in tmp:
		hours.append(item[0])

	print "Pass one: "
	print hours
	tmp = hours
	hours = []
	for item in tmp:
		if item.__contains__('='):
			hours.append(item)
	
	print "Pass two: ", hours
	tmp = hours
	hours = []
	newline = '\r\n'
	for item in tmp:
		if item.__contains__(newline):
			s = item.split(newline)
			for hour in s:
				hours.append(hour)
		else:
			hours.append(item)
	for item in hours:
		if item.__contains__(':'):
			line = item.strip(':')
			i = hours.index(item)
			hours[i] = line
	
	print "Pass three: ", hours
	tmp = hours
	hours = []
	for item in tmp:
		hours.append(item.split('='))
	
	if hours.__contains__(['']):
		for i in range(0,hours.count([''])):
			hours.remove([''])
			print "Removed."	

	print "Pass four: ", hours
	tmp = hours
	hours = []
	for item in tmp:
		print item
		s = str(item[1])
		s = s.strip(' ')
		s = s.split('-')
		item[1] = s
		hours.append(item)
	return hours

# Calculates total hours. Returns a dictionary with all hours, with high and low 
# estimates by user.
def totalHours(hours):
	hourDict = {}
	for item in hours:
		item[0] = item[0].strip(' ')
		if not hourDict.has_key(item[0]):
			hourDict[item[0]] = [0,0]
	for item in hours:
		if item[1].__len__() == 1:
			hourDict[item[0]][0] = hourDict[item[0]][0] + float(item[1][0])
                        hourDict[item[0]][1] = hourDict[item[0]][1] + float(item[1][0])
		else:
                        hourDict[item[0]][0] = hourDict[item[0]][0] + float(item[1][0])
                        hourDict[item[0]][1] = hourDict[item[0]][1] + float(item[1][1])
	return hourDict

if __name__ == "__main__":
	dbname = raw_input("What database would you like to open?")
	con = openDB(dbname)
	cur = con.cursor()
	
	milestone = getMilestones(cur)
	hours = getHourFieldContents(cur, milestone)
	hours = cleanHoursArray(hours)
	hourDict = {}
	hourDict = totalHours(hours)
	print "User Name\t\tLow\t\tHigh"
	for k in hourDict.keys():
		print k, "\t\t", int(hourDict[k][0]), "\t\t", int(hourDict[k][1])

